using System;
using System.Collections.Generic;
using System.Text;
using System.Web.Security;
using AAM.SQL;
using System.Data.Common;
using Logging;
using AAM.BIZ;
using CMS.BIZ;

namespace CMS.BOP
{
    public class SqlBizMembershipProvider : MembershipProvider
    {

        private Connection connection;
        private string applicationName;
        private bool requiresQuestionAndAnswer;
        private bool requiresUniqueEmail;
        private bool enablePasswordReset;
        private bool enablePasswordRetrieval;

        public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
        {
            base.Initialize(name, config);
            string connectionStringName = config["connectionStringName"];
            connection = ConnectionManager.Instance.OpenConnection(connectionStringName);
            requiresQuestionAndAnswer = (config["requiresQuestionAndAnswer"] == "true");
            requiresUniqueEmail = (config["requiresUniqueEmail"] == "true");
            enablePasswordReset = (config["enablePasswordReset"] == "true");
            enablePasswordRetrieval = (config["enablePasswordRetrieval"] == "true");
        }

        protected BizMembershipUser CreateFromReader(DataReader reader)
        {
            int tid = (int)reader["MemberID"];
            ObjectID oid = new ObjectID(BizMembershipUser.ObjectType, new object[] { tid });
            try
            {
                IBizObject bizObj = IdentityMap.Instance[oid];
                return (BizMembershipUser)bizObj;
            }
            catch (KeyNotFoundException)
            {
                BizMembershipUser user;
                string userName = (string)reader["UserName"];
                string email = (string)reader["Email"];
                string passwordQuestion = (string)reader["PasswordQuestion"];
                string comment = (string)reader["Comment"];
                bool isApproved = (bool)reader["IsApproved"];
                bool isLockedOut = (bool)reader["IsLockedOut"];
                DateTime creationDate = (DateTime)reader["CreationDate"];
                DateTime lastLoginDate = (DateTime)reader["LastLoginDate"];
                DateTime lastActivityDate = (DateTime)reader["LastActivityDate"];
                DateTime lastPasswordChangedDate = (DateTime)reader["LastPasswordChangedDate"];
                DateTime lastLockoutDate = (DateTime)reader["LastLockoutDate"];
                user = new BizMembershipUser(
                    Name, userName, tid, email,
                    passwordQuestion, comment,
                    isApproved, isLockedOut,
                    creationDate, lastLoginDate,
                    lastActivityDate, lastPasswordChangedDate,
                    lastLockoutDate);
                IdentityMap.Instance.Register(user);
                user.LastName = (string)reader["LastName"];
                user.FirstName = (string)reader["FirstName"];
                user.ImagePath = (string)reader["ImagePath"];
                user.CultureCode = (string)reader["CultureCode"];
                user.GodfatherID = (int?)reader["GodfatherID"];
                return user;
            }
        }
        protected List<BizMembershipUser> CreateListFromReader(DataReader reader)
        {
            List<BizMembershipUser> list = new List<BizMembershipUser>();
            while (reader.Read())
            {
                list.Add(CreateFromReader(reader));
            }
            return list;
        }
        public override string ApplicationName
        {
            get { return applicationName; }
            set { applicationName = value; }
        }
        public override bool ChangePassword(string username, string oldPassword, string newPassword)
        {
            Command command = connection.CreateCommand("UPDATE MembershipUsers SET UserPassword=?, LastPasswordChangedDate=? WHERE UserName=? AND UserPassword=?");
            command.AddParameter(newPassword);
            command.AddParameter(DateTime.Now);
            command.AddParameter(username);
            command.AddParameter(oldPassword);
            int result = command.ExecuteNonQuery();
            command.Dispose();
            return result > 0;
        }
        public override bool ChangePasswordQuestionAndAnswer(string username, string password, string newPasswordQuestion, string newPasswordAnswer)
        {
            throw new Exception("The method or operation is not implemented.");
        }
        public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey, out MembershipCreateStatus status)
        {
            Command command = connection.CreateCommand("INSERT INTO MembershipUsers(UserName,UserPassword,Email,PasswordQuestion,PasswordAnswer,IsApproved) VALUES(?,?,?,?,?,?)");
            command.AddParameter(username);
            command.AddParameter(password);
            command.AddParameter(email);
            command.AddParameter(passwordQuestion);
            command.AddParameter(passwordAnswer);
            command.AddParameter(isApproved);
            status = MembershipCreateStatus.ProviderError;
            try
            {
                int result = command.ExecuteNonQuery();
                command.Dispose();
                if (result == 1)
                {
                    status = MembershipCreateStatus.Success;
                    return GetUser(username, false);
                }
            }
            catch (DbException exc)
            {
                Logger.Global.Throwing("CMS.BOP.SqlBizMembershipProvider", "CreateUser", exc);
            }
            return null;
        }
        public override bool DeleteUser(string username, bool deleteAllRelatedData)
        {
            throw new Exception("The method or operation is not implemented.");
        }
        public override bool EnablePasswordReset { get { return enablePasswordReset; } }
        public override bool EnablePasswordRetrieval { get { return enablePasswordRetrieval; } }
        public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            throw new Exception("The method or operation is not implemented.");
        }
        public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            usernameToMatch = "%" + usernameToMatch + "%";
            Command command = connection.CreateCommand("SELECT * FROM MembershipUsers WHERE UserName LIKE ? ORDER BY UserName");
            command.AddParameter(usernameToMatch);
            DataReader reader = command.ExecuteReader();
            MembershipUserCollection list = new MembershipUserCollection();
            totalRecords = 0;
            int minPage = pageIndex * pageSize;
            int maxPage = minPage + pageSize;
            while (reader.Read())
            {
                totalRecords++;
                if(totalRecords >= minPage && totalRecords < maxPage) list.Add(CreateFromReader(reader));
            }
            reader.Close();
            command.Dispose();
            return list;
        }
        public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
        {
            throw new Exception("The method or operation is not implemented.");
        }
        public List<string> GetAllUserNames()
        {
            Command command = connection.CreateCommand("SELECT UserName FROM MembershipUsers ORDER BY UserName");
            DataReader reader = command.ExecuteReader();
            List<string> list = new List<string>();
            while (reader.Read())
            {
                list.Add((string)reader["UserName"]);
            }
            reader.Close();
            command.Dispose();
            return list;
        }
        public List<string> FindUserNames(string userNameToMatch)
        {
            Command command = connection.CreateCommand("SELECT UserName FROM MembershipUsers WHERE UserName LIKE ? ORDER BY UserName");
            command.AddParameter("%" + userNameToMatch + "%");
            DataReader reader = command.ExecuteReader();
            List<string> list = new List<string>();
            while (reader.Read())
            {
                list.Add((string)reader["UserName"]);
            }
            reader.Close();
            command.Dispose();
            return list;
        }
        public override int GetNumberOfUsersOnline()
        {
            throw new Exception("The method or operation is not implemented.");
        }
        public override string GetPassword(string username, string answer)
        {
            if(!EnablePasswordRetrieval) throw new NotSupportedException();
            if(PasswordFormat.Equals(MembershipPasswordFormat.Hashed)) throw new NotSupportedException();
            Command command;
            if (RequiresQuestionAndAnswer)
            {
                command = connection.CreateCommand("SELECT UserPassword FROM MembershipUsers WHERE UserName=? AND PasswordAnswer=?");
                command.AddParameter(username);
                command.AddParameter(answer);
            }
            else
            {
                command = connection.CreateCommand("SELECT UserPassword FROM MembershipUsers WHERE UserName=?");
                command.AddParameter(username);
            }
            try
            {
                return (string)command.ExecuteScalar();
            }
            catch (Exception exc)
            {
                throw new MembershipPasswordException("unable to get password", exc);
            }
            finally
            {
                if (command != null) command.Dispose();
            }
        }
        public bool UpdateLastActivityDate(string userName, DateTime activityDate)
        {
            Command command = connection.CreateCommand("UPDATE MembershipUsers SET LastActivityDate=? WHERE UserName=?");
            command.AddParameter(activityDate);
            command.AddParameter(userName);
            int result = command.ExecuteNonQuery();
            command.Dispose();
            return result > 0;
        }
        public override MembershipUser GetUser(string username, bool userIsOnline)
        {
            Command command = connection.CreateCommand("SELECT * FROM MembershipUsers WHERE UserName=?");
            command.AddParameter(username);
            DataReader reader = command.ExecuteReader();
            MembershipUser user = null;
            if (reader.Read())
            {
                user = CreateFromReader(reader);
            }
            command.Dispose();
            if (userIsOnline && user != null)
            {
                user.LastActivityDate = DateTime.Now;
                UpdateLastActivityDate(user.UserName,user.LastActivityDate);
            }
            return user;
        }
        public override MembershipUser GetUser(object memberId, bool userIsOnline)
        {
            return GetUser((int)memberId, userIsOnline);
        }
        public MembershipUser GetUser(int memberId, bool userIsOnline)
        {
            Command command = connection.CreateCommand("SELECT * FROM MembershipUsers WHERE MemberID=?");
            command.AddParameter(memberId);
            DataReader reader = command.ExecuteReader();
            MembershipUser user = null;
            if (reader.Read())
            {
                user = CreateFromReader(reader);
            }
            command.Dispose();
            if (userIsOnline && user != null)
            {
                user.LastActivityDate = DateTime.Now;
                UpdateLastActivityDate(user.UserName, user.LastActivityDate);
            }
            return user;
        }
        public override string GetUserNameByEmail(string email)
        {
            throw new Exception("The method or operation is not implemented.");
        }
        public override int MaxInvalidPasswordAttempts
        {
            get { throw new Exception("The method or operation is not implemented."); }
        }
        public override int MinRequiredNonAlphanumericCharacters
        {
            get { throw new Exception("The method or operation is not implemented."); }
        }
        public override int MinRequiredPasswordLength
        {
            get { throw new Exception("The method or operation is not implemented."); }
        }
        public override int PasswordAttemptWindow
        {
            get { throw new Exception("The method or operation is not implemented."); }
        }
        public override MembershipPasswordFormat PasswordFormat { get { return MembershipPasswordFormat.Clear; } }
        public override string PasswordStrengthRegularExpression
        {
            get { throw new Exception("The method or operation is not implemented."); }
        }
        public override bool RequiresQuestionAndAnswer
        {
            get { return requiresQuestionAndAnswer; }
        }
        public override bool RequiresUniqueEmail
        {
            get { return requiresUniqueEmail; }
        }
        public override string ResetPassword(string username, string answer)
        {
            throw new Exception("The method or operation is not implemented.");
        }
        public bool LockUser(string userName)
        {
            Command command = connection.CreateCommand("UPDATE MembershipUsers SET IsLockedOut=?, "
               + "LastLockoutDate=? WHERE UserName=?");
            command.AddParameter(true);
            command.AddParameter(DateTime.Now);
            command.AddParameter(userName);
            int result = command.ExecuteNonQuery();
            command.Dispose();
            if (result > 0)
            {
                BizMembershipUser user = (BizMembershipUser)GetUser(userName, false);
                IdentityMap.Instance.Unregister(user.ID);
            }
            return result > 0;
        }
        public override bool UnlockUser(string userName)
        {
            Command command = connection.CreateCommand("UPDATE MembershipUsers SET IsLockedOut=? "
               + "WHERE UserName=?");
            command.AddParameter(false);
            command.AddParameter(userName);
            int result = command.ExecuteNonQuery();
            command.Dispose(); 
            if (result > 0)
            {
                BizMembershipUser user = (BizMembershipUser)GetUser(userName, false);
                IdentityMap.Instance.Unregister(user.ID);
            }
            return result > 0;
        }
        public override void UpdateUser(MembershipUser user)
        {
            BizMembershipUser buser = (BizMembershipUser)user;
            Command command = connection.CreateCommand("UPDATE MembershipUsers SET UserName=?, "
                +"Email=? ,PasswordQuestion=?, Comment=?, IsApproved=?, IsLockedOut=?, "
                +"LastLoginDate=?, LastActivityDate=?, LastLockoutDate=?, LastName=?, FirstName=?, "
                +"CultureCode=?, ImagePath=?, GodfatherID=? "
                +"WHERE MemberID=?");
            command.AddParameter(user.UserName);
            command.AddParameter(user.Email);
            command.AddParameter(user.PasswordQuestion);
            command.AddParameter(user.Comment);
            command.AddParameter(user.IsApproved);
            command.AddParameter(user.IsLockedOut);
            command.AddParameter(user.LastLoginDate);
            command.AddParameter(user.LastActivityDate);
            command.AddParameter(user.LastLockoutDate);
            command.AddParameter(buser.LastName);
            command.AddParameter(buser.FirstName);
            command.AddParameter(buser.CultureCode);
            command.AddParameter(buser.ImagePath);
            command.AddParameter(buser.GodfatherID);
            command.AddParameter(user.ProviderUserKey);
            int result = command.ExecuteNonQuery();
            command.Dispose();
        }
        public override bool ValidateUser(string username, string password)
        {
            Command command = connection.CreateCommand("UPDATE MembershipUsers SET LastLoginDate=?, LastActivityDate=? WHERE UserName=? AND UserPassword=? AND IsApproved=? AND IsLockedOut=?");
            command.AddParameter(DateTime.Now);
            command.AddParameter(DateTime.Now);
            command.AddParameter(username);
            command.AddParameter(password);
            command.AddParameter(true);
            command.AddParameter(false);
            int result = command.ExecuteNonQuery();
            command.Dispose();
            return result > 0;
        }
        public int CountUsers(bool isApproved, bool isLockedOut)
        {
            Command command = connection.CreateCommand("SELECT COUNT(*) FROM MembershipUsers WHERE IsApproved=? AND IsLockedOut=?");
            command.AddParameter(isApproved);
            command.AddParameter(isLockedOut);
            object obj = command.ExecuteScalar();
            return obj == null ? 0 : (int)obj;
        }
        public List<string> GetAllUserNames(bool isApproved, bool isLockedOut)
        {
            Command command = connection.CreateCommand("SELECT UserName FROM MembershipUsers WHERE IsApproved=? AND IsLockedOut=?");
            command.AddParameter(isApproved);
            command.AddParameter(isLockedOut);
            List<string> list = new List<string>();
            DataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                list.Add((string)reader["UserName"]);
            }
            return list;
        }
        public int CountUsersOnline(DateTime time)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public List<BizMembershipUser> ReadAllByGodfatherID(int? godfatherID)
        {
            Command command;
            if (godfatherID == null)
            {
                command = connection.CreateCommand("SELECT * WHERE GodfatherID IS NULL");
            }
            else
            {
                command = connection.CreateCommand("SELECT * WHERE GodfatherID=?");
                command.AddParameter(godfatherID);
            }
            DataReader reader = command.ExecuteReader();
            List<BizMembershipUser> list = CreateListFromReader(reader);
            reader.Close();
            command.Dispose();
            return list;
        }

        public List<BizMembershipUser> ReadAllUserNameIn(List<string> userNames) 
        {
            if (userNames.Count == 0) return new List<BizMembershipUser>();
            StringBuilder builder = new StringBuilder();
            bool coma = false;
            foreach (string username in userNames)
            {
                if (coma) builder.Append(",");
                else coma = true;
                builder.Append("'" + username + "'");
            }
            Command command = connection.CreateCommand("SELECT * FROM MembershipUsers WHERE UserName IN ("+builder.ToString()+")");
            DataReader reader = command.ExecuteReader();
            List<BizMembershipUser> list = CreateListFromReader(reader);
            reader.Close();
            command.Dispose();
            return list;
        }
        public List<BizMembershipUser> ReadAllInMessageGroup(int messageGroupID)
        {
            Command command = connection.CreateCommand("SELECT * FROM MembershipUsers WHERE MemberID IN ("+
                    "SELECT MemberID FROM MessageGroupItems WHERE MessageGroupID=?"+
                ")");
            command.AddParameter(messageGroupID);
            DataReader reader = command.ExecuteReader();
            List<BizMembershipUser> list = CreateListFromReader(reader);
            reader.Close();
            command.Dispose();
            return list;
        }
    }
}
